package mytest.wd3;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;

public class Test_13 {
    public static void main(String[] args) {
        String dateStr = "2024-11-25";
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        DateTimeFormatter formatter2 = DateTimeFormatter.ofPattern("yyyyMM");
        LocalDate localDate = LocalDate.parse(dateStr, formatter);
        String currentMonth = localDate.format(formatter2);
        String previousMonth1 = localDate.minusMonths(1).format(formatter2);
        String previousMonth2 = localDate.minusMonths(2).format(formatter2);
        String DIAL_TASK_MIDDLE_SQL_REGISTER = "SELECT\n" +
                "    t.task_id,\n" +
                "    t.create_time,\n" +
                "    t.cus_id,\n" +
                "    t.phone,\n" +
                "    t.state,\n" +
                "    t.dial_status\n" +
                "FROM\n" +
                "    (select distinct hash_phone\n" +
                "\t\tfrom t_business_achieve_data\n" +
                "\t\tWHERE date_str = '%s'\n" +
                "AND achieve_type = 0) bad\n" +
                "INNER JOIN\n" +
                "(\n" +
                "    SELECT\n" +
                "        a.task_id,\n" +
                "    a.create_time,\n" +
                "    a.cus_id,\n" +
                "    a.phone,\n" +
                "    a.state,\n" +
                "    a.dial_status\n" +
                "    FROM\n" +
                "        t_dial_task_%s a\n" +
                "    LEFT JOIN t_cus_cat_achieve b ON a.cus_cat = b.cus_cat \n" +
                "        AND a.business_id = b.business_id \n" +
                "        AND a.user_classify = b.user_classify \n" +
                "    WHERE\n" +
                "        b.achieve = 0 \n" +
                "\t\t\t\tAND a.dial_status not in (5, 16)\n" +
                "    UNION ALL\n" +
                "    SELECT\n" +
                "         a.task_id,\n" +
                "    a.create_time,\n" +
                "    a.cus_id,\n" +
                "    a.phone,\n" +
                "    a.state,\n" +
                "    a.dial_status\n" +
                "    FROM\n" +
                "        t_dial_task_%s a\n" +
                "    LEFT JOIN t_cus_cat_achieve b ON a.cus_cat = b.cus_cat \n" +
                "        AND a.business_id = b.business_id \n" +
                "        AND a.user_classify = b.user_classify \n" +
                "    WHERE\n" +
                "        b.achieve = 0\n" +
                "\t\t\t\tAND a.dial_status not in (5, 16)\n" +
                "    UNION ALL\n" +
                "    SELECT\n" +
                "         a.task_id,\n" +
                "    a.create_time,\n" +
                "    a.cus_id,\n" +
                "    a.phone,\n" +
                "    a.state,\n" +
                "    a.dial_status\n" +
                "    FROM\n" +
                "        t_dial_task_%s a\n" +
                "    LEFT JOIN t_cus_cat_achieve b ON a.cus_cat = b.cus_cat \n" +
                "        AND a.business_id = b.business_id \n" +
                "        AND a.user_classify = b.user_classify \n" +
                "    WHERE\n" +
                "        b.achieve = 0 \n" +
                "\t\t\t\tAND a.dial_status not in (5, 16)\n" +
                ") t\n" +
                "ON bad.hash_phone = t.phone\n";

        String DIAL_TASK_MIDDLE_SQL_APPLY = "SELECT\n" +
                "    t.task_id,\n" +
                "    t.create_time,\n" +
                "    t.cus_id,\n" +
                "    t.phone,\n" +
                "    t.state,\n" +
                "    t.dial_status\n" +
                "FROM\n" +
                "    (select distinct user_id\n" +
                "\t\tfrom t_business_achieve_data\n" +
                "\t\tWHERE date_str = '%s'\n" +
                "AND achieve_type = 1) bad\n" +
                "INNER JOIN\n" +
                "(\n" +
                "    SELECT\n" +
                "        a.task_id,\n" +
                "    a.create_time,\n" +
                "    a.cus_id,\n" +
                "    a.phone,\n" +
                "    a.state,\n" +
                "    a.dial_status\n" +
                "    FROM\n" +
                "        t_dial_task_%s a\n" +
                "    LEFT JOIN t_cus_cat_achieve b ON a.cus_cat = b.cus_cat \n" +
                "        AND a.business_id = b.business_id \n" +
                "        AND a.user_classify = b.user_classify \n" +
                "    WHERE\n" +
                "        b.achieve = 1 \n" +
                "\t\t\t\tAND a.dial_status not in (5, 16)\n" +
                "    UNION ALL\n" +
                "    SELECT\n" +
                "         a.task_id,\n" +
                "    a.create_time,\n" +
                "    a.cus_id,\n" +
                "    a.phone,\n" +
                "    a.state,\n" +
                "    a.dial_status\n" +
                "    FROM\n" +
                "        t_dial_task_%s a\n" +
                "    LEFT JOIN t_cus_cat_achieve b ON a.cus_cat = b.cus_cat \n" +
                "        AND a.business_id = b.business_id \n" +
                "        AND a.user_classify = b.user_classify \n" +
                "    WHERE\n" +
                "        b.achieve = 1\n" +
                "\t\t\t\tAND a.dial_status not in (5, 16)\n" +
                "    UNION ALL\n" +
                "    SELECT\n" +
                "         a.task_id,\n" +
                "    a.create_time,\n" +
                "    a.cus_id,\n" +
                "    a.phone,\n" +
                "    a.state,\n" +
                "\t\ta.dial_status\n" +
                "    FROM\n" +
                "        t_dial_task_%s a\n" +
                "    LEFT JOIN t_cus_cat_achieve b ON a.cus_cat = b.cus_cat \n" +
                "        AND a.business_id = b.business_id \n" +
                "        AND a.user_classify = b.user_classify \n" +
                "    WHERE\n" +
                "        b.achieve = 1 \n" +
                "\t\t\t\tAND a.dial_status not in (5, 16)\n" +
                ") t\n" +
                "ON bad.user_id = t.cus_id";

        String dialTaskSqlRegister = String.format(DIAL_TASK_MIDDLE_SQL_APPLY, dateStr, currentMonth, previousMonth1, previousMonth2);
        System.out.println(dialTaskSqlRegister);
    }


}
